package vn.hocweb.dao.dao_implement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vn.hocweb.dao.dao_interface.IDanhMucDAO;
import vn.hocweb.entity.DanhMuc;

public class DanhMucDAO extends GenericDAO implements IDanhMucDAO{

	@Override
	public DanhMuc findByID(Long id) {
		Connection connection = getConnection();
		DanhMuc result = null;
		String sql = "select * from danhmuc where madanhmuc = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, id);
			ResultSet effected = pre.executeQuery();
			if(effected.next()){
				long maDanhMuc = effected.getLong("madanhmuc");
				
				String ten = effected.getString("ten");
				String ghiChu = effected.getString("ghichu");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				long maBaiHoc = effected.getLong("mabaihoc");
				long maDanhMucCha = effected.getLong("madanhmuccha");
				
				result = new DanhMuc(thuTu, noiBat, hienThi, maDanhMuc,
						maBaiHoc, ten, ghiChu, maDanhMucCha);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public List<DanhMuc> findAll(int firstItem, int batchSize) {
		Connection connection = getConnection();
		List<DanhMuc> result = new ArrayList<DanhMuc>();
		String sql = "select * from danhmuc limit ?, ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, firstItem);
			pre.setLong(2, batchSize);
			ResultSet effected = pre.executeQuery();
			while (effected.next()){
				long maDanhMuc = effected.getLong("madanhmuc");
				
				String ten = effected.getString("ten");
				String ghiChu = effected.getString("ghichu");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				long maBaiHoc = effected.getLong("mabaihoc");
				long maDanhMucCha = effected.getLong("madanhmuccha");
				
				DanhMuc row = new DanhMuc(thuTu, noiBat, hienThi, maDanhMuc,
						maBaiHoc, ten, ghiChu, maDanhMucCha);
				result.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public int size() {
		Connection connection = getConnection();
		int result = 0;
		String sql = "select count(*) from danhmuc";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			ResultSet effected = pre.executeQuery();
			if(effected.next()){
				result = effected.getInt(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean save(DanhMuc entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "insert into danhmuc (ten, ghichu, thutu, noibat, hienthi, " +
				"mabaihoc, madanhmuccha) " +
				"values (?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setString(1, entity.getTen());
			pre.setString(2, entity.getGhiChu());
			pre.setInt(3, entity.getThuTu());
			pre.setBoolean(4, entity.isNoiBat());
			pre.setBoolean(5, entity.isHienThi());
			pre.setLong(6, entity.getBaiHoc().getMaBaiHoc());
			pre.setLong(7, entity.getDanhMuc().getMaDanhMuc());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean delete(DanhMuc entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "delete from danhmuc where madanhmuc = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, entity.getMaDanhMuc());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean remove(DanhMuc entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "update danhmuc set hienthi = ? where madanhmuc = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setBoolean(1, false);
			pre.setLong(2, entity.getMaDanhMuc());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean restore(DanhMuc entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "update danhmuc set hienthi = ? where madanhmuc = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setBoolean(1, true);
			pre.setLong(2, entity.getMaDanhMuc());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public List<DanhMuc> findAll_danhSachDanhMuc(long maBH, int firstItem, int batchSize) {
		Connection connection = getConnection();
		List<DanhMuc> result = new ArrayList<DanhMuc>();
		String sql = "select * from danhmuc where mabaihoc = ? limit ?, ?";
		PreparedStatement pre;
		try {
			pre = connection.prepareStatement(sql);
			pre.setLong(1, maBH);
			pre.setInt(2, firstItem);
			pre.setInt(3, batchSize);
			ResultSet effected = pre.executeQuery();
			while (effected.next()){
				long maDanhMuc = effected.getLong("madanhmuc");
				
				String ten = effected.getString("ten");
				String ghiChu = effected.getString("ghichu");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				long maBaiHoc = effected.getLong("mabaihoc");
				long maDanhMucCha = effected.getLong("madanhmuccha");
				
				DanhMuc row = new DanhMuc(thuTu, noiBat, hienThi, maDanhMuc,
						maBaiHoc, ten, ghiChu, maDanhMucCha);
				result.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

}
